<?php
/**
 * Created by PhpStorm.
 * User: 戎大富
 * Date: 2020/6/22
 * Time: 9:16
 * 线上租金缴纳
 */
namespace app\admin\controller;
use think\Db;
use think\Request;
class Online extends Base {
    public function index(){
        if(request()->isAjax ()){
            $keyword = trim(input('keyword'), ' ');
            $where = [];
            if(isset($keyword) && $keyword != ''){
                $where['uuu.user_phone|uuu.user_username|uxh.house_num'] = ['like', '%' . $keyword . '%'];
            }
            $where['a.pay_cate'] =5;
            $where['a.pay_status'] =2;
            $page = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $count = Db::name('pay')->alias('a')
                ->join('up_xq_house uxh','uxh.house_id = a.pay_house_id','left')
                ->join('up_u_user uuu','uuu.user_id = a.pay_user_id','left')
                ->where($where)
                ->count();// 获取总条数
            $count1 = Db::name('pay')->alias('a')
                ->join('up_xq_house uxh','uxh.house_id = a.pay_house_id','left')
                ->join('up_u_user uuu','uuu.user_id = a.pay_user_id','left')
                ->where($where)
                ->column('pay_id');// 获取总条数
            $lists = Db::name('pay')->alias('a')
                ->join('up_xq_house uxh','uxh.house_id = a.pay_house_id','left')
                ->join('up_u_user uuu','uuu.user_id = a.pay_user_id','left')
                ->where($where)
                ->field('a.*,uxh.house_num,uuu.user_username,uuu.user_phone')
                ->page($page,$limits)
                ->order('a.pay_id desc')
                ->select();
            $str = '';
            if($count1){
                $str = implode($count1, ',');
            }

            return json(['code' => 220, 'msg' => '', 'count' => $count, 'count1' => $str, 'data' => $lists]);
        }
        return $this->fetch();
    }
    /**
     * 导入Excel
     * @return mixed
     */
    public function excelIn(){
        set_time_limit(0);
        if(request()->file('file')){
            $file = request()->file('file');
            $info  = $file->getInfo();
            $file_path = $info['tmp_name'];
            //取出文件后缀
            $file_type = explode(".",$info['name']);
            $file_type = end($file_type);
            vendor("PHPExcel.PHPExcel.IOFactory");
            if( $file_type == 'xls' ){
                $inputFileType = 'Excel5';    //这个是读 xls的
            }else{
                $inputFileType = 'Excel2007';//这个是计xlsx的
            }
            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($file_path);
            $objWorksheet = $objPHPExcel->getActiveSheet();
            $highestRow = $objWorksheet->getHighestRow();
            $time = date('Y-m-d H:i:s');
            Db::startTrans();
            try{
                for ($i=2;$i<=$highestRow;$i++) {
                    $b = $objPHPExcel->getActiveSheet ()->getCell ("B" . $i)->getValue ();//获取B列的值
                    $c = $objPHPExcel->getActiveSheet ()->getCell ("C" . $i)->getValue ();//获取C列的值
                    $d = $objPHPExcel->getActiveSheet ()->getCell ("D" . $i)->getValue ();//获取D列的值
                    $e = $objPHPExcel->getActiveSheet ()->getCell ("E" . $i)->getValue ();//获取E列的值

                    //查询这个房产下面所有审核通过的用户
                    $where['xq_sh_status'] = 2;
                    $where['house_id'] = $b;
                    $userInfo = Db::table('up_xq_property')->distinct(true)->where($where)->column('xq_user_id');
                    //查询这个房产下面的信息
                    $map['a.house_id'] = $b;
                    $lists = Db::name('xq_house')->alias('a')
                        ->join('up_xq ux','ux.xq_id =a.xq_id','left')
                        ->join('up_xq_block uxb','uxb.block_id =a.block_id','left')
                        ->join('up_xq_unit uxn','uxn.unit_id =a.unit_id','left')
                        ->join('up_xq_room uxr','uxr.room_id =a.room_id','left')
                        ->where($map)
                        ->order('a.house_time desc')
                        ->field('a.*,ux.xq_name,uxb.block_name,uxn.unit_name,uxr.room_name')
                        ->find();
                    //插入到消息表
                    $dataAll['n_title'] = $lists['xq_name'].'  '.$lists['block_name'].'-'.$lists['room_name'];
                    $dataAll['n_type'] = 2;
                    $dataAll['n_user_id'] = ','.implode(',',$userInfo).',';
                    $dataAll['n_read'] = ',0,';
                    $dataAll['n_delete'] = ',0,';
                    $dataAll['n_time'] = $time;
                    $dataAll['n_classify'] = 2;
                    $dataAll['n_classify_type'] = 1;
                    $dataAll['n_classify_status'] = $d;
                    $dataAll['n_money'] = $e;
                    Db::table('up_notice')->insert($dataAll);



                    $data['pay_title'] =$c;
                    $data['house_id'] = $b;
                    $data['pay_status'] = $d;
                    $data['pay_money'] = $e;
                    $data['pay_type'] = 1;
                    $data['pay_time'] = $time;
                    Db::table('up_xq_pay')->insertGetId($data);
                }
                $result['excel_name'] = '物业费导入';
                $result['excel_time'] = $time;
                $result['excel_admin'] = session('uid');
                Db::table('up_excel')->insertGetId($result);
                Db::commit();
                echo '/admin/wy/index';
            }catch(\Exception $e){
                Db::rollback();
            }

        }
        return $this->fetch('wy/excelin');
    }

    /**
     * 导出
     * @return \think\response\Json
     */
    public function outExcel(){
        $ids = input('ids');
        if(empty($ids))
            return json(['code' => 100, 'msg' => '请选择需要导出的数据']);
        $where['pay_id'] = array('IN',$ids);
        $yy = Db::name('pay')->alias('a')
            ->join('up_xq_house uxh','uxh.house_id = a.pay_house_id','left')
            ->join('up_u_user uuu','uuu.user_id = a.pay_user_id','left')
            ->where($where)
            ->field('a.*,uxh.house_num,uuu.user_username,uuu.user_phone')
            ->order('a.pay_time asc')
            ->select();
        $data = array();
        foreach($yy as $k => $v){
            $data[$k]['user_username'] =$v['user_username'];
            $data[$k]['user_phone'] =$v['user_phone'];
            $data[$k]['pay_time'] =$v['pay_time'];
            $data[$k]['pay_price'] =$v['pay_price'];
            $data[$k]['house_num'] =$v['house_num'];
            if($v['pay_type'] == 1){
                $data[$k]['pay_type'] ='微信';
            }else{
                $data[$k]['pay_type'] ='支付宝';
            }
            $data[$k]['pay_third_num'] = $v['pay_third_num'];
        }
        $cellname = [
            ['user_username','用户名',20,'LEFT'],
            ['user_phone','手机号码',20,'LEFT'],
            ['pay_time','缴费时间',20,'LEFT'],
            ['pay_price','缴费金额',20,'LEFT'],
            ['house_num','房产编号',20,'LEFT'],
            ['pay_type','支付方式',20,'LEFT'],
            ['pay_third_num','第三方交易号',20,'LEFT'],
        ];
        $res = exportExcel('租金缴纳', 'Looks', $cellname, $data);
        return json($res);
    }
}